import pathlib
import sqlite3
from openpyxl import load_workbook
from basedata import *
import mpmath
import time
import basedata

log=basedata.log


root=pathlib.Path(__file__).parent
db=sqlite3.connect(str(root/"cj.db"))
cur = db.cursor()
try:
    sql="DROP TABLE main;"
    cur.execute(sql)
except:
    log.error("main删除失败,按ENTER继续")
    input()
    
else:
    log.info("main删除成功")
try:
    sql='''
        CREATE TABLE IF NOT EXISTS "main" (
        "考生号" INTEGER NOT NULL,
        "姓名" TEXT,
        "班级" TEXT,
        "语文" real,
        "数学" real,
        "英语" real,
        "物理" real,
        "化学" real,
        "生物" real,
        "道法" real,
        "历史" real,
        "地理" real,
        "总分" real,
        PRIMARY KEY ("考生号")
        );

    '''
    cur.execute(sql)
except:
    log.error("main创建失败,按ENTER退出")
    exit()
else:
    log.info("main创建成功")

db.commit()
subject=['班级','考生号','姓名']+zkm

'''
result=cur.execute("select 考生号, 班级 from main")
for i in result.fetchall():
    cur.execute("UPDATE main SET '班级' = '{0}' WHERE 考生号 = {1}".format(int(float(i[1])),i[0]))
db.commit()
'''
def getData(wb_active):

    headers=[]
    datas=[]
    Countsubject=0
    wb_row=wb_active.rows
    print(wb_active.max_column)
    for i in range(wb_active.max_column):
        title=wb_active.cell(row=3,column=i+1).value

        if(restoreSubjectAlias(title) is not None):
            headers.append((i+1,restoreSubjectAlias(title)))
        if(restoreSubjectAlias(title) in zkm):
            Countsubject +=1

    print(headers)
    for i in range(5,wb_active.max_row+1):
        dt=[]
        zf_t=mpmath.mpf(0)
        if(wb_active.cell(row=i,column=4).value == None):
            print("null")
        else:
            for n in headers:
                dt.append(conversionDataType(n[1],wb_active.cell(row=i,column=n[0]).value))
                zf_t=zf_t+izh(n[1],wb_active.cell(row=i,column=n[0]).value)
            if(dt.count(None) == Countsubject):
                dt.append(None)
            else:
                dt.append(str(zf_t))
            datas.append(dt)
            print(dt)
    # print(datas)
    return (headers,datas)

def insertData(headers,datas):
    t=[]
    t1=[]
    for i in headers:
        t.append(f"`{i[1]}`")
        t1.append("?")
    t.append("总分")
    t1.append("?")
    t=",".join(t)
    t1=",".join(t1)
    print(t)
    sql=f"REPLACE INTO main({t}) VALUES({t1});"
    print(sql)
    cur.executemany(sql,datas)
    db.commit()
    print(sql)

def restoreSubjectAlias(alias):
    if(alias in subject):
        return alias
    else:
        for k in subjectAlias:
            if(k not in subject):
                log.error(f"<{k}>科目不在统计中")
            else:
                if(alias in subjectAlias[k]):
                    return k
    return None


def izh(title,data):###总分计算
    if(title in zkm ):
        if(data=="缺考" or data=="" or data==None) :
            return 0
        else:
            if(ScoreConversion):
                return mpmath.mpf(zh(title,data))
            else:
                return mpmath.mpf(data)
    else:
        return 0
def missedExam(dt):
    pass
def conversionDataType(title,data):
    if(data=="缺考" or data=="" or data==None) :
        return None
        # return basedata.MissingExamination
            # return 'is NULL'
    if(title=="班级" or title=="考生号"):
        if(isinstance(data,str)):
            return "{0}".format(str(data.replace("班","")))
        else:
            return "{0}".format(str(int(data)))
    if(title=="姓名"):
        return f"{data}"
    return str(data)

dirs=root.glob('*.xlsx')
for f in dirs:
    print(f)
    if ("~$" in str(f) ):
        print("error")
    else:
        wb = load_workbook(str(f))
        wb_active=wb.active
        (headers,datas)=getData(wb_active)
        insertData(headers,datas)
# a=input("已导入数据，按Enter退出")
      
for i in range(4):
    print('\r',f"已导入数据，{3-i}秒后退出",end='')
    time.sleep(1)
